﻿' ------------------------------------------------------------------------------
' A Connection contains information necessary to make a connection to SQL Server,
' and can both make and disconnect the connection. When a connection is
' established, it queries the database version and then determines the proper
' coordinate order to be used with for well-known text geometry.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Class Connection

    Public Server As String
    Public Database As String
    Public Auth As EnumConnectionAuth
    Public Login As String
    Public Password As String
    Public CoordinateOrder As EnumCoordinateOrder

    Public Event ConnectionChanged()
    Public Event StartToConnect()
    Public Event ConnectSucceeded()
    Public Event ConnectionAttemptFailed(ByVal ex As Exception)

    Private m_connected As Boolean
    Private m_connection As SqlConnection

#Region " properties "
    Public ReadOnly Property Connected() As Boolean
        Get
            Return m_connected
        End Get
    End Property

    Public ReadOnly Property SqlConnection() As SqlConnection
        Get
            Return m_connection
        End Get
    End Property
#End Region

    Public Sub New()
        Server = Nothing
        Database = Nothing
        Auth = EnumConnectionAuth.eUnknown
        Login = Nothing
        Password = Nothing
        CoordinateOrder = EnumCoordinateOrder.eUnknown

        m_connected = False
        m_connection = Nothing
    End Sub

    Public Sub Connect()

        If Server = "" Or Database = "" Then
            ReportTrace("No connection information.")
            Exit Sub
        End If

        Dim oldconn As Boolean = Connected
        m_connection = New SqlConnection(ConnectionString())
        ReportTrace("opening connection:")
        ReportTrace(SqlConnection.ConnectionString)

        RaiseEvent StartToConnect()
        Try
            m_connection.Open()
            m_connected = True
            RaiseEvent ConnectSucceeded()
        Catch ex As Exception
            m_connected = False
            RaiseEvent ConnectionAttemptFailed(ex)
        End Try

        If Connected Then ReportTrace("connected to database")
        If Not Connected Then ReportTrace("connection failed")

        If Connected Then DetermineCoordinateOrder()
        If oldconn <> Connected Then RaiseEvent ConnectionChanged()
    End Sub

    ' Determines the latitude/longitude coordinate order to use for well-known text
    ' representations of geography. See comments in the Enums module.

    Private Sub DetermineCoordinateOrder()
        Dim ver As String = ""
        Dim cmd As SqlCommand = Nothing
        Try
            cmd = SqlConnection.CreateCommand
            cmd.CommandText = "SELECT SERVERPROPERTY('ProductVersion')"
            ver = CType(cmd.ExecuteScalar(), String)

            Dim parts() As String = CType(ver, String).Split("."c)
            If parts.Length <> 4 Then Return
            Dim n(3) As Integer
            For i As Integer = 0 To 3
                n(i) = Integer.Parse(parts(i))
            Next

            ' version 10.0.1300.13 is the last version to use the old lat/lon ordering
            If n(0) > 10 Then GoTo newversion
            If n(0) < 10 Then GoTo oldversion
            If n(1) > 0 Then GoTo newversion
            If n(2) > 1300 Then GoTo newversion
            If n(2) < 1300 Then GoTo oldversion
            If n(3) > 13 Then GoTo newversion
            GoTo oldversion

        Catch ex As Exception
            ReportException(ex)
        End Try

newversion:
        CoordinateOrder = EnumCoordinateOrder.eLongitudeLatitude
        ReportSqlCommand("server version: " & ver & ", longitude/latitude ordering", cmd)
        Return
oldversion:
        CoordinateOrder = EnumCoordinateOrder.eLatitudeLongitudeObsolete
        ReportSqlCommand("server version: " & ver & ", obsolete latitude/longitude ordering", cmd)
        Return
    End Sub

    Public Sub Disconnect()
        If Not m_connected Then Return
        m_connection.Close()
        ReportTrace("disconnected")
        m_connected = False
        RaiseEvent ConnectionChanged()
    End Sub

    Public ReadOnly Property ConnectionString() As String
        Get
            Dim builder As New SqlConnectionStringBuilder
            builder.Item("Data Source") = Server
            builder.Item("Initial Catalog") = Database
            If Auth = EnumConnectionAuth.eSqlServer Then
                builder.Item("Integrated Security") = "false"
                builder.Item("User ID") = Login
                builder.Item("Password") = Password
            ElseIf Auth = EnumConnectionAuth.eWindows Then
                builder.Item("Integrated Security") = "sspi"
            End If
            builder.Item("Type System Version") = "SQL Server 2008"
            Return builder.ConnectionString
        End Get
    End Property

    ' convenience method so clients can use
    '    myconnection.CreateCommand()
    ' instead of
    '    myconnection.SqlCommand.CreateCommand()

    Public Function CreateCommand() As SqlCommand
        If Not m_connected Then Return Nothing
        Return SqlConnection.CreateCommand()
    End Function

End Class
